﻿''' <summary>
''' 科目余额类
''' 对应表 - T_BalanceMoney
''' </summary>
Public Class ClsTBalanceMoneyDbAccess
    Inherits ClsBaseDbAccess

    Public Sub New(ByVal s As String)
        MyBase.New(s, TABLE_NAME_T_BALANCE_MONEY)
    End Sub

    Public Sub New(ByVal s As String, ByVal n As String)
        MyBase.New(s, n)
    End Sub

    ''' <summary>
    ''' 删除指定期间的科目余额
    ''' </summary>
    ''' <param name="wp"></param>
    ''' <returns></returns>
    Public Function DeleteBalanceByWorkPeriod(ByVal wp As String) As Integer

        Dim tmpRetInt As Integer

        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "Delete * from [" & Me.AppTableName & "] "
            tmpSql &= "where  [WorkPeriod]=@WorkPeriod "

            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = wp

            tmpRetInt = tmpDbAccess.UpdateRecord(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - DeleteBalanceByWorkPeriod"

        End Try

        Return tmpRetInt

    End Function

    ''' <summary>
    ''' 更新余额表中年初数数据
    ''' </summary>
    ''' <param name="acct"></param>
    ''' <returns></returns>
    Public Function UpdateYearMoney(ByVal acct As String, ByVal oldYearMoney As Decimal, ByVal newYearMoney As Decimal) As Integer

        Dim tmpRetInt As Integer

        Try

            Dim tmpSql As String
            Dim tmpYearDif As Decimal = newYearMoney - oldYearMoney '计算两个年初数差值
            Dim tmpParas(1) As OleDb.OleDbParameter
            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "Update [" & Me.AppTableName & "] set "
            tmpSql &= " [Year]=@Year, [BeginBalance]=[BeginBalance]+@YearDif,[Balance]=[Balance]+@YearDif "
            tmpSql &= "where  [AcctCode]=@AcctCode "

            tmpParas(0) = New OleDb.OleDbParameter("@Year", OleDb.OleDbType.Decimal)
            tmpParas(1) = New OleDb.OleDbParameter("@AcctCode", OleDb.OleDbType.VarWChar)
            tmpParas(2) = New OleDb.OleDbParameter("@YearDif", OleDb.OleDbType.Decimal)

            tmpParas(0).Value = newYearMoney
            tmpParas(1).Value = acct
            tmpParas(2).Value = tmpYearDif

            tmpRetInt = tmpDbAccess.UpdateRecord(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - InsertAccountSum"

        End Try

        Return tmpRetInt

    End Function


    ''' <summary>
    ''' 取表中指定科目最后一个期间数据
    ''' </summary>
    ''' <param name="acct"></param>
    ''' <returns></returns>
    Public Function GetNearestBalanceMoneyByAcct(ByVal acct As String) As ClsTBalanceMoney


        Dim tmpCls As ClsTBalanceMoney = Nothing

        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpList As List(Of ClsTBalanceMoney) = New List(Of ClsTBalanceMoney)()
            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "select * from [" & Me.AppTableName & "] where [AcctCode]=@AcctCode Order by [BalanceId] desc "


            'tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(0) = New OleDb.OleDbParameter("@AcctCode", OleDb.OleDbType.VarWChar)

            'tmpParas(0).Value = wp
            tmpParas(0).Value = acct

            tmpList = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql, tmpParas)), List(Of ClsTBalanceMoney))

            If tmpList.Count > 0 Then
                tmpCls = tmpList.Item(0)
            End If

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - GetAccountSumInPeriod"

        End Try

        Return tmpCls

    End Function

    Public Function GetAcctBalanceMoneyInPeriod(ByVal wp As String) As List(Of ClsTBalanceMoney)

        Dim tmpList As List(Of ClsTBalanceMoney) = New List(Of ClsTBalanceMoney)()

        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "select * from [" & Me.AppTableName & "] where [WorkPeriod]=@WorkPeriod "
            'tmpSql &= " and [AcctCode]=@AcctCode "


            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)

            tmpParas(0).Value = wp

            tmpList = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql, tmpParas)), List(Of ClsTBalanceMoney))


            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - GetAccountSumInPeriod"

        End Try

        Return tmpList

    End Function

    ''' <summary>
    ''' 返回指定期间和指定编号 的科目余额
    ''' </summary>
    ''' <param name="wp"></param>
    ''' <param name="acct"></param>
    ''' <returns></returns>
    Public Function GetAcctBalanceMoneyInPeriodByAcct(ByVal wp As String, ByVal acct As String) As ClsTBalanceMoney

        Dim tmpCls As ClsTBalanceMoney = Nothing

        Try

            Dim tmpSql As String
            Dim tmpParas(1) As OleDb.OleDbParameter
            Dim tmpList As List(Of ClsTBalanceMoney) = New List(Of ClsTBalanceMoney)()
            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "select * from [" & Me.AppTableName & "] where [WorkPeriod]=@WorkPeriod "
            tmpSql &= " and [AcctCode]=@AcctCode "


            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(1) = New OleDb.OleDbParameter("@AcctCode", OleDb.OleDbType.VarWChar)

            tmpParas(0).Value = wp
            tmpParas(1).Value = acct

            tmpList = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql, tmpParas)), List(Of ClsTBalanceMoney))

            If tmpList IsNot Nothing AndAlso tmpList.Count > 0 Then
                tmpCls = tmpList.Item(0)
            End If

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - GetAccountSumInPeriod"

        End Try

        Return tmpCls

    End Function

    ''' <summary>
    ''' 更新总账数据
    ''' </summary>
    ''' <param name="tmpCls"></param>
    ''' <returns></returns>
    Public Function UpdateBalanceMoney(ByVal tmpCls As ClsTBalanceMoney) As Integer

        Dim tmpRetInt As Integer

        Try

            Dim tmpSql As String
            Dim tmpParas(13) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "Update [" & Me.AppTableName & "] set "
            tmpSql &= "[WorkPeriod]=@WorkPeriod,[AcctCode]=@AcctCode,[AcctLevel]=@AcctLevel,"
            tmpSql &= "[IfLastLevel]=@IfLastLevel,[AcctName]=@AcctName,[Year]=@Year,"
            tmpSql &= "[BeginBalance]=@BeginBalance,[CreditSum]=@CreditSum,[CreditTotal]=@CreditTotal,"
            tmpSql &= "[DebitSum]=@DebitSum,[DebitTotal]=@DebitTotal,[Balance]=@Balance,"
            tmpSql &= "[BalanceDirection]=@BalanceDirection "
            tmpSql &= " where [BalanceId]=@BalanceId "

            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(1) = New OleDb.OleDbParameter("@AcctCode", OleDb.OleDbType.VarWChar)
            tmpParas(2) = New OleDb.OleDbParameter("@AcctLevel", OleDb.OleDbType.Integer)
            tmpParas(3) = New OleDb.OleDbParameter("@IfLastLevel", OleDb.OleDbType.Integer)
            tmpParas(4) = New OleDb.OleDbParameter("@AcctName", OleDb.OleDbType.VarWChar)
            tmpParas(5) = New OleDb.OleDbParameter("@Year", OleDb.OleDbType.Decimal)
            tmpParas(6) = New OleDb.OleDbParameter("@BeginBalance", OleDb.OleDbType.Decimal)
            tmpParas(7) = New OleDb.OleDbParameter("@CreditSum", OleDb.OleDbType.Decimal)
            tmpParas(8) = New OleDb.OleDbParameter("@CreditTotal", OleDb.OleDbType.Decimal)
            tmpParas(9) = New OleDb.OleDbParameter("@DebitSum", OleDb.OleDbType.Decimal)
            tmpParas(10) = New OleDb.OleDbParameter("@DebitTotal", OleDb.OleDbType.Decimal)
            tmpParas(11) = New OleDb.OleDbParameter("@Balance", OleDb.OleDbType.Decimal)
            tmpParas(12) = New OleDb.OleDbParameter("@BalanceDirection", OleDb.OleDbType.VarWChar)
            tmpParas(13) = New OleDb.OleDbParameter("@BalanceId", OleDb.OleDbType.Integer)

            With tmpCls

                tmpParas(0).Value = .WorkPeriod
                tmpParas(1).Value = .AcctCode
                tmpParas(2).Value = .AcctLevel
                tmpParas(3).Value = .IfLastLevel
                tmpParas(4).Value = .AcctName
                tmpParas(5).Value = .Year
                tmpParas(6).Value = .BeginBalance
                tmpParas(7).Value = .CreditSum
                tmpParas(8).Value = .CreditTotal
                tmpParas(9).Value = .DebitSum
                tmpParas(10).Value = .DebitTotal
                tmpParas(11).Value = .Balance
                tmpParas(12).Value = .BalanceDirection
                tmpParas(13).Value = .BalanceId

            End With

            tmpRetInt = tmpDbAccess.UpdateRecord(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - InsertAccountSum"

        End Try

        Return tmpRetInt

    End Function

    ''' <summary>
    ''' 插入一行新的科目汇总记录
    ''' </summary>
    ''' <param name="tmpCls"></param>
    ''' <returns></returns>
    Public Function InsertBalanceMoney(ByVal tmpCls As ClsTBalanceMoney) As Integer

        Dim tmpRetInt As Integer

        Try

            Dim tmpSql As String
            Dim tmpParas(12) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "Insert into [" & Me.AppTableName & "] ("
            tmpSql &= "[WorkPeriod],[AcctCode],[AcctLevel],[IfLastLevel],[AcctName],"
            tmpSql &= "[Year],[BeginBalance],[CreditSum],[CreditTotal],[DebitSum],"
            tmpSql &= "[DebitTotal],[Balance],[BalanceDirection]"
            tmpSql &= ") Values ("
            tmpSql &= "@WorkPeriod,@AcctCode,@AcctLevel,@IfLastLevel,@AcctName,"
            tmpSql &= "@Year,@BeginBalance,@CreditSum,@CreditTotal,@DebitSum,"
            tmpSql &= "@DebitTotal,@Balance,@BalanceDirection"
            tmpSql &= ")"

            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(1) = New OleDb.OleDbParameter("@AcctCode", OleDb.OleDbType.VarWChar)
            tmpParas(2) = New OleDb.OleDbParameter("@AcctLevel", OleDb.OleDbType.Integer)
            tmpParas(3) = New OleDb.OleDbParameter("@IfLastLevel", OleDb.OleDbType.Integer)
            tmpParas(4) = New OleDb.OleDbParameter("@AcctName", OleDb.OleDbType.VarWChar)
            tmpParas(5) = New OleDb.OleDbParameter("@Year", OleDb.OleDbType.Decimal)
            tmpParas(6) = New OleDb.OleDbParameter("@BeginBalance", OleDb.OleDbType.Decimal)
            tmpParas(7) = New OleDb.OleDbParameter("@CreditSum", OleDb.OleDbType.Decimal)
            tmpParas(8) = New OleDb.OleDbParameter("@CreditTotal", OleDb.OleDbType.Decimal)
            tmpParas(9) = New OleDb.OleDbParameter("@DebitSum", OleDb.OleDbType.Decimal)
            tmpParas(10) = New OleDb.OleDbParameter("@DebitTotal", OleDb.OleDbType.Decimal)
            tmpParas(11) = New OleDb.OleDbParameter("@Balance", OleDb.OleDbType.Decimal)
            tmpParas(12) = New OleDb.OleDbParameter("@BalanceDirection", OleDb.OleDbType.VarWChar)

            With tmpCls

                tmpParas(0).Value = .WorkPeriod
                tmpParas(1).Value = .AcctCode
                tmpParas(2).Value = .AcctLevel
                tmpParas(3).Value = .IfLastLevel
                tmpParas(4).Value = .AcctName
                tmpParas(5).Value = .Year
                tmpParas(6).Value = .BeginBalance
                tmpParas(7).Value = .CreditSum
                tmpParas(8).Value = .CreditTotal
                tmpParas(9).Value = .DebitSum
                tmpParas(10).Value = .DebitTotal
                tmpParas(11).Value = .Balance
                tmpParas(12).Value = .BalanceDirection

            End With


            tmpRetInt = tmpDbAccess.InsertData(tmpSql, tmpParas, True)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - InsertAccountSum"

        End Try

        Return tmpRetInt

    End Function

    Public Overrides Function ConvertRecordToClass(reader As OleDb.OleDbDataReader) As Object
        If reader Is Nothing Then
            Return Nothing
        End If

        Dim tmpList As List(Of ClsTBalanceMoney) = New List(Of ClsTBalanceMoney)

        Try

            Dim tmpCls As ClsTBalanceMoney

            While reader IsNot Nothing AndAlso reader.Read()

                tmpCls = New ClsTBalanceMoney()

                With tmpCls

                    .BalanceId = reader.GetInt32(0)
                    .WorkPeriod = reader.GetString(1)
                    .AcctCode = reader.GetString(2)
                    .AcctLevel = reader.GetInt16(3)
                    .IfLastLevel = reader.GetBoolean(4)
                    .AcctName = reader.GetString(5)
                    .Year = reader.GetDecimal(6)
                    .BeginBalance = reader.GetDecimal(7)
                    .CreditSum = reader.GetDecimal(8)
                    .CreditTotal = reader.GetDecimal(9)
                    .DebitSum = reader.GetDecimal(10)
                    .DebitTotal = reader.GetDecimal(11)
                    .Balance = reader.GetDecimal(12)
                    .BalanceDirection = reader.GetString(13)

                End With

                tmpList.Add(tmpCls)

            End While

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTGeneralMoneyLedgerDbAccess - ConvertRecordToClass"

        End Try

        Return tmpList

    End Function
End Class
